package com.sqlquery.demo.dao.Impl;
import com.sqlquery.demo.dao.UserDao;
import com.sqlquery.demo.entity.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.*;
import org.springframework.stereotype.Repository;

import javax.persistence.Id;
import java.util.*;
@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public int add(User user) {
        return jdbcTemplate.update("INSERT INTO user(id, name,password,email) VALUE(?,?,?,?)", user.getId(), user.getName(), user.getPassword(), user.getEmail());
    }
    @Override
    public int addRoleById(User_Role user_role) {
        return jdbcTemplate.update("INSERT INTO user_role(id, user_id,role_id) VALUE(?,?,?)",  user_role.getId(),user_role.getUser_id(), user_role.getRole_id());
    }
    @Override
    public int delRoleById(User_Role user_role) {
        return jdbcTemplate.update("DELETE FROM user_role where user_id = ? AND role_id = ?", user_role.getUser_id(), user_role.getRole_id());
    }
    @Override
    public int update(User user) {
        return jdbcTemplate.update("UPDATE user set name = ?,password = ?,email = ? where id=?",  user.getName(), user.getPassword(), user.getEmail(),user.getId());
    }
    @Override
    public List<Map<String, Object>> findByName(String name) {
        //模糊查询
        List<Map<String, Object>> user = jdbcTemplate.queryForList("SELECT * FROM user WHERE name like ?", new Object[]{"%" + name + "%"});
        if (user != null && user.size() > 0)
            return user;
        else
            return null;
    }
    @Override
    public User findById(Integer id) {
        List<User> user = jdbcTemplate.query("SELECT * FROM user WHERE id = ?", new Object[]{id}, new BeanPropertyRowMapper<>(User.class));
        if (user != null && user.size() > 0)
            return user.get(0);
        else
            return null;
    }
    @Override
    public int delete(Integer id) {
        return jdbcTemplate.update("DELETE FROM user where id = ?", id);
    }
    @Override
    public List<Map<String, Object>> findRoleByUserName(String name) {
        List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT role.role FROM user_role,role WHERE user_role.user_id = (SELECT id FROM user WHERE user.name = ?) AND role.id = user_role.role_id;", new Object[]{name});
        if (list != null && list.size() > 0)
            return list;
        else
            return null;
    }
    @Override
    public List<Map<String, Object>> findUserByRoleName(String name) {
        List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT user.id,user.name,user.password,user.email FROM user_role,user WHERE user_role.role_id = (SELECT id FROM role WHERE role.role = ?) AND user.id = user_role.user_id;", new Object[]{name});
        if (list != null && list.size() > 0)
            return list;
        else
            return null;
    }
    @Override
    public List<Map<String, Object>> findRoleByUserId(Integer id) {
        List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT * FROM user_role,role WHERE user_role.user_id = ? AND role.id = user_role.role_id;", new Object[]{id});
        if (list != null && list.size() > 0)
            return list;
        else
            return null;
    }
    @Override
    public List<Map<String, Object>> findUserByRoleId(Integer id) {
        List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT * FROM user_role,`user` WHERE user_role.role_id = ? AND user.id = user_role.user_id;", new Object[]{id});
        if (list != null && list.size() > 0)
            return list;
        else
            return null;
    }
    @Override
    public List<Map<String, Object>> findAll(Integer page) {
        int index = (page - 1) * 5;
        List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT * FROM user LIMIT ?,5;", new Object[]{index});
        if (list != null && list.size() > 0)
            return list;
        else
            return null;
    }
}